import datetime
import math
import os
import sys
import time

# 将工程目录加入包扫描
currPath = os.path.abspath(os.path.dirname(__file__))
srcPath = os.path.split(currPath)[0]
rootPath = os.path.split(srcPath)[0]
projectPath = os.path.split(rootPath)[0]
sys.path.append(projectPath)

from src.db.FCDB import FCDB
from src.db.LocalDb import LocalDb
from src.utils import CommonUtils
from src.utils.CommonUtils import cleanScreen
from src.utils.LogUtils import Logger


class SyncFcProStockChgSub:
    def __init__(self, logger: Logger, localDb: LocalDb, fcdb: FCDB):
        self.logger = logger
        self.localDb = localDb
        self.fcdb = fcdb
        self.size = 10000
        self.isAll = False
        self.enterpriseMapper = {}
        self.securityMapper = {}

    def __queryLocalMaxTmstamp__(self):
        sql = "SELECT max(tmstamp) as tmstamp from fc_pro_stock_chg_sub"
        r = self.localDb.QueryOne(sql)
        if r is None or r["tmstamp"] is None:
            return 0
        return r["tmstamp"]

    def __loadAllSecurities__(self):
        sql = """
        SELECT SECURITYID,ISSUECOMPCODE, RAISEMODE FROM tq_bd_newestbasicinfo
        union 
        SELECT SECURITYID,ISSUECOMPCODE, RAISEMODE FROM tq_bd_otcbasicinfo
        UNION 
        SELECT SECURITYID,COMPCODE, '1' FROM tq_bd_bonddebt
        """
        return self.fcdb.Query(sql)

    def __loadSecurities__(self, securityId):
        sql = """
        SELECT SECURITYID,ISSUECOMPCODE, RAISEMODE FROM tq_bd_newestbasicinfo where SECURITYID =  %s
        union 
        SELECT SECURITYID,ISSUECOMPCODE, RAISEMODE FROM tq_bd_otcbasicinfo where SECURITYID =  %s
        UNION 
        SELECT SECURITYID,COMPCODE, '1' FROM tq_bd_bonddebt where SECURITYID =  %s
        """
        r = self.fcdb.QueryOne(sql, (securityId, securityId, securityId))
        if r is not None:
            return r
        else:
            return None

    def __queryTqBdProStockChgSub__(self, maxTMSTAMP, size):
        sql = """
            SELECT a.ID,a.SECURITYID,a.CHGDATE,a.CHGTYPE,a.CHGAMT,a.TMSTAMP
            FROM tq_bd_prostockchgsub  a
            WHERE a.tmstamp > %s
            ORDER BY a.tmstamp asc
            LIMIT %s
            """
        if maxTMSTAMP is None:
            maxTMSTAMP = 0
        return self.fcdb.Query(sql, (maxTMSTAMP, size))

    def __queryTqBdProStockChgSubCount__(self, maxTMSTAMP):
        sql = """
            SELECT count(1) as cnt
            FROM tq_bd_prostockchgsub  a
            WHERE a.tmstamp > %s
            """
        if maxTMSTAMP is None:
            maxTMSTAMP = 0
        r = self.fcdb.QueryOne(sql, (maxTMSTAMP,))
        return r["cnt"]

    def __loadEnterprise__(self):
        sql = """
            SELECT 
            e.credit_code, e.comp_code, e.ent_type, e.external_eval, e.foreign_external_eval, e.qe_eval,
            r.province_code, r.city_code,r.county_code
            FROM ent_enterprise e
            LEFT JOIN regions r ON e.platform_region_code = r.`code`
        """
        return self.localDb.Query(sql)

    def __update__(self, updateDatas):
        sql = """
            REPLACE INTO fc_pro_stock_chg_sub
            (id, security_id, chg_year, chg_month, chg_date, 
            chg_type, chg_amt_increase, chg_amt_decrease, tmstamp, 
            issuer_code, raise_mode, issuer_external_rank, issuer_qe_rank, 
            is_platform, province_code, city_code, county_code)
            VALUES
            (%(id)s,%(security_id)s,%(chg_year)s,%(chg_month)s,%(chg_date)s,
            %(chg_type)s,%(chg_amt_increase)s,%(chg_amt_decrease)s,%(tmstamp)s,
            %(issuer_code)s,%(raise_mode)s,%(issuer_external_rank)s,%(issuer_qe_rank)s,
            %(is_platform)s,%(province_code)s,%(city_code)s,%(county_code)s);
        """
        return self.localDb.ExecMany(sql, updateDatas)

    def __getEnterprise__(self, compcode):
        if compcode is None:
            return None
        if compcode in self.enterpriseMapper.keys():
            return self.enterpriseMapper.get(compcode)
        return None

    def __getSecurity__(self, securityId):
        # 全量模式
        if self.isAll:
            if securityId in self.securityMapper.keys():
                return self.securityMapper.get(securityId)
            else:
                return None
        else:
            if securityId not in self.securityMapper.keys():
                security = self.__loadSecurities__(securityId)
                self.securityMapper[securityId] = security
                self.logger.info(f"缓存债券 {securityId}")
            return self.securityMapper.get(securityId)

    def run(self):

        # 解析参数
        self.isAll = CommonUtils.isCommandParam(sys.argv, "all")["exist"]

        if self.isAll:
            self.logger.warn(CommonUtils.createColorText("全量更新模式", "red"))

        maxTMSTAMP = 0
        if not self.isAll:
            maxTMSTAMP = self.__queryLocalMaxTmstamp__()
        self.logger.info("同步 fc_pro_stock_chg_sub maxTMSTAMP=%d" % maxTMSTAMP)

        # 检查待更新数据量
        cnt = self.__queryTqBdProStockChgSubCount__(maxTMSTAMP)
        if cnt == 0:
            self.logger.info("==============没有更多的数据=================")
            return

        # 缓存企业
        self.logger.info("缓存企业")
        enterpriseList = self.__loadEnterprise__()
        self.enterpriseMapper = {d["comp_code"]: d for d in enterpriseList}
        self.logger.info(f"缓存企业 {len(self.enterpriseMapper)}")

        # 全量模式下，提前全部缓存
        if self.isAll:
            self.logger.info("缓存全部债券")
            securityList = self.__loadAllSecurities__()
            self.securityMapper = {d["SECURITYID"]: d for d in securityList}
            self.logger.info(f"缓存全部债券 {len(self.securityMapper)}")

        total = 0
        updated = 0

        while True:
            remoteDatas = self.__queryTqBdProStockChgSub__(maxTMSTAMP, self.size)
            if len(remoteDatas) == 0:
                self.logger.info("==============没有更多的数据=================")
                break
            self.logger.info("加载数据 %d" % len(remoteDatas))
            total += len(remoteDatas)

            updateDatas = []
            for r in remoteDatas:
                security = self.__getSecurity__(r["SECURITYID"])
                if security is None:
                    a = 0
                enterprise = None if security is None else self.__getEnterprise__(security["ISSUECOMPCODE"])
                updateDatas.append({
                    "id": r["ID"],
                    "security_id": r["SECURITYID"],
                    "chg_year": r["CHGDATE"][:4],
                    "chg_month": r["CHGDATE"][4:6],
                    "chg_date": f"{r['CHGDATE'][:4]}-{r['CHGDATE'][4:6]}-{r['CHGDATE'][6:]}",
                    "chg_type": r["CHGTYPE"],
                    "chg_amt_increase": r["CHGAMT"] if r["CHGAMT"] > 0 else None,
                    "chg_amt_decrease": -r["CHGAMT"] if r["CHGAMT"] < 0 else None,
                    "tmstamp": r["TMSTAMP"],
                    "issuer_code": enterprise["credit_code"] if enterprise is not None else None,
                    "raise_mode": security["RAISEMODE"] if security is not None else None,
                    "issuer_external_rank": enterprise["external_eval"] if enterprise is not None else None,
                    "issuer_qe_rank": enterprise["qe_eval"] if enterprise is not None else None,
                    "is_platform": 1 if enterprise is not None and enterprise["ent_type"] == 'T002' else 0,
                    "province_code": enterprise["province_code"] if enterprise is not None else None,
                    "city_code": enterprise["city_code"] if enterprise is not None else None,
                    "county_code": enterprise["county_code"] if enterprise is not None else None
                })

            updated += len(updateDatas)
            if len(updateDatas) > 0:
                self.__update__(updateDatas)
                self.logger.info(f"更新数据 {len(updateDatas)}")

            if len(remoteDatas) < self.size:
                self.logger.info("处理结束")
                break

            # 更新maxTMSTAMP
            maxTMSTAMP = remoteDatas[len(remoteDatas) - 1]["TMSTAMP"]

        self.logger.info(f"总共加载数据 {total}条，实际更新 {updated}条")


def __main__():
    logger = Logger()
    cleanScreen()

    t = time.perf_counter()
    logger.info("同步 财汇债券存量变动辅助表（产品表） 开始")

    localDb = LocalDb(logSql=False, autocommit=True)
    fcdb = FCDB(logSql=False, autocommit=True)

    sync = SyncFcProStockChgSub(logger=logger, fcdb=fcdb, localDb=localDb)
    sync.run()

    cast = (time.perf_counter() - t)
    m = math.floor(cast / 60)
    s = cast - m * 60
    logger.info('同步 财汇债券存量变动辅助表（产品表） 结束 %s 总耗时: %d分%.3f秒' % (datetime.datetime.now(), m, s))


# 程序入口
if __name__ == "__main__":
    __main__()
